import datetime
import logging

from sqlTools.mysqlConnect import mysql_db
from tools.configmanager import config

# danjimysql连接池
mysql_danji_root = mysql_db(host=config.get("dan_ji.ip"), port=config.get("dan_ji.mysql.port"), database="mysql",
                            user="root", pwd=config.get("dan_ji.mysql.rootPwd"), )

neirongdb = config.get("dan_ji.mysql.database.0")
mysql_danji_db = mysql_db(host=config.get('dan_ji.ip'), port=config.get('dan_ji.mysql.port'),
                          database=neirongdb.get('dbname'), user=neirongdb.get('user'), pwd=neirongdb.get('pwd'), )

ob_nrlj = config.get("dan_ji.OceanBase.database.1")
ob_nrlj_db = mysql_db(host=config.get('dan_ji.ip'), port=config.get('dan_ji.OceanBase.port'),
                      database=ob_nrlj.get('dbname'), user=ob_nrlj.get('user'), pwd=ob_nrlj.get('pwd'), )
ob_airflow = config.get("dan_ji.OceanBase.database.2")
ob_airflow_db = mysql_db(host=config.get('dan_ji.ip'), port=config.get('dan_ji.OceanBase.port'),
                         database=ob_airflow.get('dbname'), user=ob_airflow.get('user'), pwd=ob_airflow.get('pwd'), )


def clean_ob_nrlj(db_instance=ob_nrlj_db, dbname='nrlj'):
    """
    清理OceanBase，租户nrlj， 遍历清空 dj_  lj_ 开头的表即可,对于mysql系列，_也是个特殊字符，需要\转义。
    show tables WHERE tables_in_nrlj LIKE 'dj\_%' OR tables_in_nrlj LIKE 'lj\_%';
    """
    db_instance.execute_sql("SET FOREIGN_KEY_CHECKS=0;")
    sql = f"""
show tables 
WHERE tables_in_{dbname} LIKE %s 
OR tables_in_nrlj LIKE %s;"""
    params = ('dj\_%', 'lj\_%')
    cursor = db_instance.execute_sql(sql, params)
    for row in cursor:
        try:
            sql2 = f"truncate `{row[0]}`;"
            logging.info(sql2)
            db_instance.execute_sql(sql2)
        except Exception:
            logging.exception("")
            break
    cursor.connection.commit()
    db_instance.execute_sql("SET FOREIGN_KEY_CHECKS=1;")
    db_instance.close()


def recreate_db_airflow(db_instance=ob_airflow_db):
    """
    重建database
    """
    sql = "SELECT DATABASE();"
    cursor = db_instance.execute_sql(sql)
    for row in cursor:
        try:
            logging.info(row[0])
            sql2 = f"DROP DATABASE IF EXISTS `{row[0]}`;"
            logging.info(sql2)
            db_instance.execute_sql(sql2)
        except Exception:
            logging.exception("")
            break
    cursor.connection.commit()
    sql3 = f"CREATE DATABASE `{row[0]}`;"
    logging.info(sql3)
    db_instance.execute_sql(sql3)
    db_instance.close()


def drop_table_airflow(db_instance=ob_airflow_db):
    db_instance.execute_sql("SET FOREIGN_KEY_CHECKS=0;")
    sql = "show tables;"
    cursor = db_instance.execute_sql(sql)
    for row in cursor:
        try:
            sql2 = f"DROP TABLE IF EXISTS `{row[0]}`;"
            logging.info(sql2)
            db_instance.execute_sql(sql2)
        except Exception:
            logging.exception("")
            break
    cursor.connection.commit()
    db_instance.execute_sql("SET FOREIGN_KEY_CHECKS=1;")
    db_instance.close()


def mysql_drop_split(db_instance=mysql_danji_db):
    """
    遍历nrlj_big_table_split_info记录，然后drop table
    """
    cursor = db_instance.execute_sql("SELECT * FROM nrlj_big_table_split_info;")
    rows = cursor.fetchall()
    for row in rows:
        logging.info(row[3])
        try:
            db_instance.execute_sql(f"DROP TABLE {row[3]};")
        except Exception:
            logging.exception("")
    cursor.connection.commit()
    db_instance.close()


def optimize_table(table_name, db_instance=mysql_danji_db):
    """
    执行过大量删除记录或者变长字段缩短后，磁盘空间并不会释放，而是等新数据填充。
    如果没有大量新数据补充，请手动释放磁盘空间。
    """
    logging.warning(f"执行 optimize table {table_name}; 时，可能耗时很久，请耐心等待! ")
    sql = f"OPTIMIZE TABLE neirongdb.{table_name}"
    logging.info(sql)
    cursor = db_instance.execute_sql(sql)
    cursor.connection.commit()
    db_instance.close()


def mysql_clean_binlog(db_instance=mysql_danji_root):
    """
    减少binlog大小,需要root权限
    """
    now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    sql = f"purge master logs before '{now}'"
    logging.info(sql)
    cursor = db_instance.execute_sql(sql)
    cursor.connection.commit()
    db_instance.close()


def mysql_script(db_instance=mysql_danji_db, sql_file=None):
    with open(sql_file, "r") as f:  # 打开文件
        data = f.read()
        lines = data.splitlines()
        sql_data = ""
        # 将--注释开头的全部过滤，将空白行过滤
        for line in lines:
            if len(line) == 0:
                continue
            elif line.startswith("--"):
                continue
            else:
                sql_data += line
        sql_list = sql_data.split(";")[:-1]
        sql_list = [x.replace("\n", " ") if "\n" in x else x for x in sql_list]
        logging.info("------------------- SQL ------------------------")
        logging.info(sql_list)
        with db_instance.cursor() as cursor:
            for sql in sql_list:
                cursor.execute(sql)
            cursor.connection.commit()
        db_instance.close()
    logging.info("--------------------------------------------------")
